﻿Namespace SchoolClick.Database.DAL

    Public Interface IAñoEscolar
        ReadOnly Property IAñoEscolar As IAñoEscolar

        Function IsEdit() As Boolean
        Function IsCrearAñoEscolar() As Boolean
        Function GetNextYear() As String
        Sub CrearNuevoAñoEscolar(FInicio As Date, FFinal As Date)
        Function GetInfoAñoEscolar(año As String) As ANIOS_ESCOLARES
        Function GetInfoAñoEscolar() As ANIOS_ESCOLARES
        Sub UpdateAñoEscolar(FechaInicio As Date, FechaFinal As Date)
        Function GetYearEdit() As String
        Function GetTotalAlumnosByGrado(Año As String, GradoEspecialidad As String) As INFORMACION_ALUMNOS_X_GRADO
        Function GetResumenImportNuevoIngreso(Año As String) As IEnumerable(Of COMPOSICION_GRADOS_SECCIONES)
        Sub ImportAlumnosNuevoIngreso(Año As String)
        Sub Metodo1_AsignarAlumnosASecion(Año As String)
        Sub CloseEditNewAnioEscolar()
        Function GetStatusAñoEscolar() As INFORMACION_ANIOESCOLAR
        Sub OpenPeriodo(FechaCierre As Date, HoraCierre As DateTime)
        Function GetPeriodoEvaluativoActivo() As PERIODO
        Sub ClosePeriodo()
        Sub CloseAnioEscolar()
        Sub ClosePeriodoRecuperacion()
        Sub ClosePeriodoRecuperacionXtra()
        Function GetPeriodosClose() As IEnumerable(Of PERIODO)
        Function GetAños() As IEnumerable(Of ANIOS_ESCOLARES)
        Function GetPeriodosCloseAndActivo() As IEnumerable(Of PERIODO)
    End Interface

    Partial Public Class SchoolClickDAL
        Implements IAñoEscolar

        Public ReadOnly Property IAñoEscolar As IAñoEscolar Implements IAñoEscolar.IAñoEscolar
            Get
                Return DirectCast(Me, IAñoEscolar)
            End Get
        End Property

        Private Function IsCrearAñoEscolar() As Boolean Implements IAñoEscolar.IsCrearAñoEscolar
            Dim cnn As New SchoolClickEntities
            Try
                cnn.IS_POSSIBLE_CREATE_NEW_YEAR()
                Return True
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function IsEdit() As Boolean Implements IAñoEscolar.IsEdit
            Dim cnn As New SchoolClickEntities
            Try
                Dim val = cnn.IS_EDIT_ANIOESCOLAR.Single
                Return If(val.HasValue, val.Value, False)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetNextYear() As String Implements IAñoEscolar.GetNextYear
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GET_NEXT_YEAR_ESCOLAR.Single
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub CrearNuevoAñoEscolar(FInicio As Date, FFinal As Date) Implements IAñoEscolar.CrearNuevoAñoEscolar
            Dim cnn As New SchoolClickEntities
            Try
                cnn.NEW_ANIO_ESCOLAR(FInicio, FFinal, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function GetInfoAñoEscolar() As ANIOS_ESCOLARES Implements IAñoEscolar.GetInfoAñoEscolar
            Dim cnn As New SchoolClickEntities
            Try
                Dim año = CInt(IAñoEscolar.GetNextYear) - 1
                Return GetInfoAñoEscolar(año)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetInfoAñoEscolar(año As String) As ANIOS_ESCOLARES Implements IAñoEscolar.GetInfoAñoEscolar
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.ANIOS_ESCOLARES.Where(Function(ae) ae.ANIO_ESCOLAR = año).SingleOrDefault
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub UpdateAñoEscolar(FechaInicio As Date, FechaFinal As Date) Implements IAñoEscolar.UpdateAñoEscolar
            Dim cnn As New SchoolClickEntities
            Try
                cnn.UPT_ANIO_ESCOLAR(FechaInicio, FechaFinal, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function GetYearEdit() As String Implements IAñoEscolar.GetYearEdit
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GET_AÑO_ESCOLAR_EDIT().SingleOrDefault()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetTotalAlumnosByGrado(Año As String, GradoEspecialidad As String) As INFORMACION_ALUMNOS_X_GRADO Implements IAñoEscolar.GetTotalAlumnosByGrado
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.INFO_TOTAL_ALUMNOS_POR_GRADOS(Año, GradoEspecialidad).Single
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetResumenImportNuevoIngreso(Año As String) As IEnumerable(Of COMPOSICION_GRADOS_SECCIONES) Implements IAñoEscolar.GetResumenImportNuevoIngreso
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.COMPOSICION_GRADOS_SECCIONES.Where(Function(cg) cg.ANIO_ESCOLAR = Año).ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub ImportAlumnosNuevoIngreso(Año As String) Implements IAñoEscolar.ImportAlumnosNuevoIngreso
            Dim cnn As New SchoolClickEntities
            Try
                cnn.IMPORT_NUEVO_INGRESO_BY_YEAR(Año, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Sub Metodo1_AsignarAlumnosASecion(Año As String) Implements IAñoEscolar.Metodo1_AsignarAlumnosASecion
            Dim cnn As New SchoolClickEntities
            Try
                cnn.METHOD_1_RANDOM_ALUMNO_SECCION(Año, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Sub CloseEditNewAnioEscolar() Implements IAñoEscolar.CloseEditNewAnioEscolar
            Dim cnn As New SchoolClickEntities
            Try
                cnn.OP_CLOSE_EDICION_NEW_ANIO_ESCOLAR(ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function GetStatusAñoEscolar() As INFORMACION_ANIOESCOLAR Implements IAñoEscolar.GetStatusAñoEscolar
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.INFORMACION_ANIOESCOLAR.Single
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Public Sub OpenPeriodo(FechaCierre As Date, HoraCierre As Date) Implements IAñoEscolar.OpenPeriodo
            Dim cnn As New SchoolClickEntities
            Try
                cnn.OP_OPEN_PERIODO(New Date(FechaCierre.Year, FechaCierre.Month, FechaCierre.Day, HoraCierre.Hour, HoraCierre.Minute, 0),
                                    ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Public Function GetPeriodoEvaluativoActivo() As PERIODO Implements IAñoEscolar.GetPeriodoEvaluativoActivo
            Dim cnn As New SchoolClickEntities
            Try
                'Obtenemos el Año y Periodo
                Dim Año = cnn.ANIOS_ESCOLARES.Max(Function(ae) ae.ANIO_ESCOLAR),
                    Pe = cnn.ANIOS_ESCOLARES.Where(Function(ae) ae.ANIO_ESCOLAR = Año) _
                                                 .Select(Function(ae) ae.PACTIVO).Single
                Dim IDPeriodo = String.Format("{0}-{1}", Pe, Año)

                'Regresamo el registro comprendido en los 2 valores encontrados
                Return cnn.PERIODOS.Where(Function(p) p.ID_PERIODO = IDPeriodo).Single
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub ClosePeriodo() Implements IAñoEscolar.ClosePeriodo
            Dim cnn As New SchoolClickEntities
            Try
                cnn.OP_CLOSE_PERIODO_ACTIVO(ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Sub CloseAnioEscolar() Implements IAñoEscolar.CloseAnioEscolar
            Dim cnn As New SchoolClickEntities
            Try
                cnn.OP_CLOSE_ANIOESCOLAR_ACTIVO(ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Sub ClosePeriodoRecuperacion() Implements IAñoEscolar.ClosePeriodoRecuperacion
            Dim cnn As New SchoolClickEntities
            Try
                cnn.OP_CLOSE_PERIODO_SUFICIENCIA(ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Sub ClosePeriodoRecuperacionXtra() Implements IAñoEscolar.ClosePeriodoRecuperacionXtra
            Dim cnn As New SchoolClickEntities
            Try
                cnn.OP_CLOSE_PERIODO_RECUPERACION_EXTRA(ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function GetPeriodosClose() As IEnumerable(Of PERIODO) Implements IAñoEscolar.GetPeriodosClose
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.PERIODOS.Where(Function(p) p.CERRADO = True).ToArray
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetAños() As IEnumerable(Of ANIOS_ESCOLARES) Implements IAñoEscolar.GetAños
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.ANIOS_ESCOLARES.Where(Function(ae) ae.EDIT_INFO_ANIO = False).ToArray
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetPeriodosCloseAndActivo() As IEnumerable(Of PERIODO) Implements IAñoEscolar.GetPeriodosCloseAndActivo
            Dim cnn As New SchoolClickEntities
            Try
                Dim r = (From p In cnn.PERIODOS
                         Where p.CERRADO = True
                         Select p).ToArray
                Dim pa = cnn.PERIODOS.Where(Function(p) p.CERRADO = False AndAlso p.F_APERTURA IsNot Nothing).ToArray

                Return r.Union(pa).OrderByDescending(Function(p) p.ANIO_ESCOLAR) _
                                  .ThenByDescending(Function(p) p.PERIODO1)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function
    End Class
End Namespace
